﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_BrowseBasicReferences')
	BEGIN
		DROP  Procedure  [esf_sso].P_BrowseBasicReferences
	END
GO

-- [esf_sso].P_BrowseBasicReferences @pBasicReferenceTypeID = 1, @pPageIndex = 0, @pPageSize = 4
-- [esf_sso].P_BrowseBasicReferences @pBasicReferenceTypeID = 1, @pPageIndex = 1, @pPageSize = 4
-- [esf_sso].P_BrowseBasicReferences @pBasicReferenceTypeID = 1, @pPageIndex = 2, @pPageSize = 4

CREATE Procedure [esf_sso].P_BrowseBasicReferences
	@pID					BIGINT = NULL
	,@pBasicReferenceTypeID	INT = NULL
	,@pBasicReferenceTypeIDs VARCHAR (MAX) = NULL
	,@pCode					VARCHAR (60) = NULL
	,@pName					VARCHAR (60) = NULL
	,@pIsEnabled			BIT = NULL
	,@pPageIndex			BIGINT = 0
	,@pPageSize				BIGINT = 30
AS

	SET NOCOUNT ON

	SELECT * FROM
		(SELECT
			ROW_NUMBER () OVER (ORDER BY VBR.Name) AS Ri
			,COUNT(*) OVER () AS RCount
			,VBR.ID
			,VBR.Code
			,VBR.Name
			,VBR.Description 
			,VBR.IsEnabled 
		FROM 
			[esf_sso].VBasicReference VBR
		WHERE 
			(@pID IS NULL OR VBR.ID = @pID)
			AND (@pCode IS NULL OR VBR.Code LIKE @pCode)
			AND (@pName IS NULL OR VBR.Name LIKE @pName)
			AND (@pIsEnabled IS NULL OR VBR.IsEnabled = @pIsEnabled)
			AND (@pBasicReferenceTypeID IS NULL OR VBR.BasicReferenceTypeID = @pBasicReferenceTypeID)
			AND (@pBasicReferenceTypeIDs IS NULL OR VBR.BasicReferenceTypeID IN (SELECT TempItem FROM [dbo].[FUBSplitString] (@pBasicReferenceTypeIDs, ',')))
		) T
	WHERE
		T.Ri BETWEEN (@pPageIndex * @pPageSize + 1) AND (@pPageIndex + 1) * @pPageSize
	ORDER BY 
		T.Ri

GO